using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using SharePointUtility.InsightServiceUtility;

namespace SharePointUtility
{
    public partial class VFAUtility : Form
    {
        public VFAUtility()
        {
            InitializeComponent();

            LoadYear();
        }
        private void LoadYear()
        {
            string[] year =  {"2009",
                         "2008",
                 "2007",
                         "2006",
                         "2005",
                         "2004",
                         "2003",
                         "2002",
                         "2001",
                         "2000",
                         "1999",
                         "1998"};

            clbYear.Items.AddRange(year);
            clbYear.CheckOnClick = true;

        }

        private void btnConvert_Click(object sender, EventArgs e)
        {
            string years = string.Empty;
            years = GetCheckedYears();
            try
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["SQLDataSource"]))
                {

                    connection.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    if (years == string.Empty)
                    {
                        cmd.CommandText = string.Format(@"SELECT P.ID PROTOCOLID, P.PROTOCOLNUMBER,P.ShortTitle, P.ModuleType,
                                                    D.[Name],D.id as documentid
                                                    FROM dbo.PROTOCOL P
                                                    INNER JOIN  dbo.PROTOCOLDOCUMENT PD ON PD.PROTOCOLID = P.ID
                                                    INNER JOIN  dbo.DOCUMENT D ON D.id = pd.documentid 
                                                    WHERE protocolnumber is not null and  substring( protocolnumber,1 ,4) in ({0})
                                                    and pd.iscurrent =  1 and  (isconvertedtopdf =0 or isconvertedtopdf is null)
                                                    and d.name not like '%.pdf%'"
                                                    , years);
                    }
                    else
                    {
                        cmd.CommandText = @"SELECT P.ID PROTOCOLID, P.PROTOCOLNUMBER,P.ShortTitle, P.ModuleType,
                                                    D.[Name],D.id as documentid
                                                    FROM dbo.PROTOCOL P
                                                    INNER JOIN  dbo.PROTOCOLDOCUMENT PD ON PD.PROTOCOLID = P.ID
                                                    INNER JOIN  dbo.DOCUMENT D ON D.id = pd.documentid 
                                                    WHERE protocolnumber is null 
                                                    and pd.iscurrent =  1 and  (isconvertedtopdf =0 or isconvertedtopdf is null)
                                                    and d.name not like '%.pdf%'";
                    }


                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);

                    // Display the ProgressBar control.
                    pBar.Visible = true;
                    // Set Minimum to 1 to represent the first file being copied.
                    pBar.Minimum = 1;
                    // Set Maximum to the total number of files to copy.
                    int maxCount = ds.Tables[0].Rows.Count;
                    pBar.Maximum = maxCount;
                    // Set the initial value of the ProgressBar.
                    pBar.Value = 1;
                    // Set the Step property to a value of 1 to represent each file being copied.
                    pBar.Step = 1;

                    string documentLibraryName = ConfigurationManager.AppSettings["DocumentLibrary"].ToString();
                    int i = 1;

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        ConvertToPDFUploadAndUpdateFlag(dr, connection, documentLibraryName);
                        // Perform the increment on the ProgressBar.
                        pBar.PerformStep();
                        pBar.Refresh();
                        //int percen8iot = (int)(((double)pBar.Value / (double)pBar.Maximum) * 100);
                        //pBar.CreateGraphics().DrawString(percent.ToString() + "%", new Font("Arial", (float)10.25, FontStyle.Regular), Brushes.Black, new PointF(pBar.Width / 2 - 10, pBar.Height / 2 - 7));
                        lbProgBar.Text = string.Format("Converted {0} documents of {1} documents", i, maxCount);
                        lbProgBar.Refresh();
                        i++;
                    }
                    lbProgBar.Text = string.Format("Converted {0} documents", maxCount);

                }
            }
            catch (Exception ex)
            {
                string fileName = ConfigurationManager.AppSettings["ErrorLog"].ToString();
                System.IO.StreamWriter file = new System.IO.StreamWriter(fileName, true);
                file.WriteLine(ex.ToString());

                file.Close();
            }
        }

        public string GetCheckedYears()
        {
            StringBuilder years = new StringBuilder();
            // Next show the object title and check state for each item selected.
            foreach (object itemChecked in clbYear.CheckedItems)
            {
                years.Append(itemChecked.ToString() + ",");

            }
            return years.ToString().TrimEnd(',');
        }

        public void ConvertToPDFUploadAndUpdateFlag(DataRow dr, SqlConnection connection, string documentLibraryName)
        {
            int docID = Convert.ToInt32(dr["DOCUMENTID"]);

            try
            {
                DocumentMigration docMigration = new DocumentMigration();
                string baseUrl = docMigration.GetURL(dr);

                string fileName = dr["Name"].ToString();
                string filExtension = System.IO.Path.GetExtension(fileName); ;
                string folderUrl = dr["PROTOCOLID"].ToString() + "/" + docID.ToString() + filExtension;
                string folderHierarchy = baseUrl + "/" + documentLibraryName + "/" + folderUrl;

                DocumentUtility DocUtility = new DocumentUtility();
                DocUtility.ConvertToPDFFromSharePoint(connection.ConnectionString, docID, folderHierarchy);

            }
            catch (Exception ex)
            {
                string fileName = ConfigurationManager.AppSettings["ErrorLog"].ToString();
                System.IO.StreamWriter file = new System.IO.StreamWriter(fileName, true);
                file.WriteLine(ex.ToString() + Environment.NewLine + docID.ToString());

                file.Close();
            }

        }

        private void btnVFA_Click(object sender, EventArgs e)
        {

            string years = string.Empty;
            years = GetCheckedYears();
         
            try
            {

                using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["SQLDataSource"]))
                {

                    connection.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    if (years != string.Empty)
                    {
                        cmd.CommandText = string.Format(@"SELECT 
                                                      distinct pp.* FROM dbo.Protocol p
                                                      inner join dbo.ProtocolProcess pp on pp.ProtocolId = p.Id
                                                      where pp.Locked = 1 and p.IsCurrent = 1 and
                                                      protocolnumber is not null and  substring( protocolnumber,1 ,4) in ({0}) "
                                                          , years);
                    }
                    else
                    {
                        cmd.CommandText = @"SELECT 
                                          distinct pp.* FROM dbo.Protocol p
                                          inner join dbo.ProtocolProcess pp on pp.ProtocolId = p.Id
                                          where pp.Locked = 1 and p.IsCurrent = 1 and 
                                          protocolnumber is null";

                    }

                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);

                    // Display the ProgressBar control.
                    pBar.Visible = true;
                    // Set Minimum to 1 to represent the first file being copied.
                    pBar.Minimum = 1;
                    // Set Maximum to the total number of files to copy.
                    int maxCount = ds.Tables[0].Rows.Count;
                    pBar.Maximum = maxCount;
                    // Set the initial value of the ProgressBar.
                    pBar.Value = 1;
                    // Set the Step property to a value of 1 to represent each file being copied.
                    pBar.Step = 1;

                    string documentLibraryName = ConfigurationManager.AppSettings["DocumentLibrary"].ToString();
                    int i = 1;
                    
                    DocumentUtility DocUtility = new DocumentUtility();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        
                        string docStagingFolder = @"\\phsweb418\DocumentStagingFolder";
                        DocUtility.GenerateAndSaveApplicationDocuments(Convert.ToInt32(dr["ID"]), docStagingFolder, Convert.ToInt32(dr["ProtocolID"]));

                        // Perform the increment on the ProgressBar.
                        pBar.PerformStep();
                        pBar.Refresh();
                        //int percen8iot = (int)(((double)pBar.Value / (double)pBar.Maximum) * 100);
                        //pBar.CreateGraphics().DrawString(percent.ToString() + "%", new Font("Arial", (float)10.25, FontStyle.Regular), Brushes.Black, new PointF(pBar.Width / 2 - 10, pBar.Height / 2 - 7));
                        lbProgBar.Text = string.Format("Converted {0} documents of {1} documents", i, maxCount);
                        lbProgBar.Refresh();
                        i++;
                        int sleepTime = string.IsNullOrEmpty(textBox1.Text) ? 0 : Convert.ToInt32(textBox1.Text) * 1000;
                        System.Threading.Thread.Sleep(sleepTime);
                    }
                    lbProgBar.Text = string.Format("Converted {0} documents", maxCount);

                }
            }
            catch (Exception ex)
            {
                string fileName = ConfigurationManager.AppSettings["ErrorLog"].ToString();
                System.IO.StreamWriter file = new System.IO.StreamWriter(fileName, true);
                file.WriteLine(ex.ToString());

                file.Close();
            }
        }

      




    }
}